package net.xju3_4.dao;

import net.xju3_4.common.utils.JdbcUtilsV1;
import net.xju3_4.common.utils.PageInfo;
import net.xju3_4.entity.Goods;
import net.xju3_4.entity.vo.GoodsVo;

import java.util.ArrayList;
import java.util.List;

public class GoodsDao {
    public boolean insert(Goods goods) {
        String sql = "insert into goods(name,price,pubdate,type_id,intro,picture,flag,create_time) values(?,?,?,?,?,?,?,?)";
        List<Object> params = new ArrayList<>();
        params.add(goods.getName());
        params.add(goods.getPrice());
        params.add(goods.getPubdate());
        params.add(goods.getTypeId());
        params.add(goods.getIntro());
        params.add(goods.getPicture());
        params.add(goods.getFlag());
        params.add(goods.getCreateTime());
        int i = JdbcUtilsV1.executeUpdate(JdbcUtilsV1.getConnection(), sql, params.toArray());
        return i>0;
    }

    public boolean delete(int id) {
        String sql = "update goods set deleted= 1 where id=?";
        int i = JdbcUtilsV1.executeUpdate(JdbcUtilsV1.getConnection(), sql, id);
        return i>0;
    }

    public boolean update(Goods goods) {
        StringBuilder sql = new StringBuilder("UPDATE goods SET ");
        List<Object> params = new ArrayList<>();

        //动态拼接非空字段
        if (goods.getName() != null && !goods.getName().isEmpty()) {
            sql.append("name = ?,");
            params.add(goods.getName());
        }
        if (goods.getPrice() != null) {
            sql.append("price = ?,");
            params.add(goods.getPrice());
        }
        if (goods.getPubdate() != null) {
            sql.append("pubdate = ?,");
            params.add(goods.getPubdate());
        }
        if (goods.getTypeId() != null) {
            sql.append("type_id = ?,");
            params.add(goods.getTypeId());
        }
        if (goods.getIntro() != null && !goods.getIntro().isEmpty()) {
            sql.append("intro = ?,");
            params.add(goods.getIntro());
        }
        if (goods.getPicture() != null && !goods.getPicture().isEmpty()) {
            sql.append("picture = ?,");
            params.add(goods.getPicture());
        }
        if (goods.getFlag() != null) {
            sql.append("flag = ?,");
            params.add(goods.getFlag());
        }
        if(!params.isEmpty()) {
            sql.deleteCharAt(sql.lastIndexOf(","));
        }

        // 添加where 条件
        sql.append(" WHERE id=?");
        params.add(goods.getId());
        int i = JdbcUtilsV1.executeUpdate(JdbcUtilsV1.getConnection(), sql.toString(), params.toArray());
        return i>0;
    }

    public PageInfo<GoodsVo> pageList(Integer pageNo, Integer pageSize, Goods goods) {
        String sql = "SELECT g.id,g.name,g.price,g.pubdate,g.intro,g.picture,g.flag,g.type_id typeId,g.create_time createTime, gs.name goodsTypeName FROM `goods` g LEFT JOIN goodstype gs ON g.type_id = gs.id WHERE g.deleted = 0 ";
        List<Object> params = new ArrayList<>();
        if (goods.getName() != null && !goods.getName().isEmpty()) {
            sql += " and g.name like ?";
            params.add("%" + goods.getName() + "%");
        }
        if (goods.getPubdate() != null) {
            sql += " and g.pubdate = ?";
            params.add(goods.getPubdate());
        }
        if (goods.getTypeId() != null) {
            sql += " and g.type_id = ?";
            params.add(goods.getTypeId());
        }
        if (goods.getFlag() != null) {
            sql += " and g.flag = ?";
            params.add(goods.getFlag());
        }
        sql += " limit ?,?";
        params.add((pageNo-1)*pageSize);
        params.add(pageSize);
        List<GoodsVo> goodsVos = JdbcUtilsV1.executeQuery(GoodsVo.class, sql, params.toArray());
        String sqlCount = "SELECT COUNT(*) FROM goods WHERE deleted =0";
        long count = JdbcUtilsV1.executeQueryOneValue(JdbcUtilsV1.getConnection(), sqlCount);
        return new PageInfo<>(pageNo,pageSize,(int)count,goodsVos);
    }
}
